﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using SaludOcupacional.Exceptions;
using SaludOcupacional.Model;
using System.Collections.ObjectModel;

namespace SaludOcupacional.DataAccess
{
    public class ConsultaDataAccess : IDisposable
    {
        private DatabaseManager dbManager;

        public void Dispose()
        {
            dbManager.Dispose();
        }
        public ConsultaDataAccess()
        {
            dbManager = new DatabaseManager();
        }

        public void CreateConsulta(ConsultaMedica consultaMedica)
        {
            if (consultaMedica == null)
            {
                throw new LoginException("Invalid Input Parameters");
            }



            string sqlSentence = "INSERT INTO [dbo].[Consulta]([ci_empleado],[fecha],[area_trabajo],[codigo_embol],[patologia],[gravedad],[origen],[accion],[externo])VALUES ( ";
            sqlSentence += "@Ci, @Fecha, @AreaTrabajo, @CodigoEmbol, @Patologia, @Gravedad, @Origen, @Accion, @Externo )";

            dbManager.Command.CommandText = sqlSentence;
            dbManager.Command.CommandType = System.Data.CommandType.Text;



            dbManager.Command.Parameters.AddWithValue("@Ci", consultaMedica.CiEmpleado);
            dbManager.Command.Parameters.AddWithValue("@Fecha", consultaMedica.Fecha);
            dbManager.Command.Parameters.AddWithValue("@AreaTrabajo", consultaMedica.AreaTrabajo);
            dbManager.Command.Parameters.AddWithValue("@CodigoEmbol", consultaMedica.CodigoEmbol);
            dbManager.Command.Parameters.AddWithValue("@Patologia", consultaMedica.Patologia);
            dbManager.Command.Parameters.AddWithValue("@Gravedad", consultaMedica.Gravedad);
            dbManager.Command.Parameters.AddWithValue("@Origen", consultaMedica.Origen);
            dbManager.Command.Parameters.AddWithValue("@Accion", consultaMedica.Accion);
            dbManager.Command.Parameters.AddWithValue("@Externo", consultaMedica.Externo);
            
            try
            {
                int value = dbManager.NonQuery();

                if (value < 1)
                {
                    throw new LoginException("The record wasn't registered");
                }
            }
            catch (Exception e)
            {
                throw new LoginException("Error creating a user" + e.Message, e);
            }

        }

        public Collection<ConsultaMedica> BuscarConsultasPorCi(int ci)
        {
            string sqlsentence = "SELECT  * FROM [Consulta] WHERE ci_empleado = @CI";

            dbManager.Command.CommandText = sqlsentence;
            dbManager.Command.Parameters.AddWithValue("@CI", ci);


            Collection<ConsultaMedica> consultas = new Collection<ConsultaMedica>();

            try
            {
                DataSet dataSet = dbManager.Query();

                if (dataSet.Tables[0].Rows.Count > 0)
                {
                    DataRowCollection reader = dataSet.Tables[0].Rows;

                    for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
                    {

                        consultas.Add(new ConsultaMedica {
                            CodConsulta = Convert.ToInt32(reader[i]["cod_consulta"], System.Globalization.CultureInfo.InvariantCulture),
                            CiEmpleado = Convert.ToInt32(reader[i]["ci_empleado"], System.Globalization.CultureInfo.InvariantCulture),
                            Fecha = reader[i]["fecha"].ToString(),
                            AreaTrabajo = reader[i]["area_trabajo"].ToString(),
                            CodigoEmbol = Convert.ToInt32(reader[i]["codigo_embol"], System.Globalization.CultureInfo.InvariantCulture),
                            Patologia = reader[i]["patologia"].ToString(),
                            Gravedad = reader[i]["gravedad"].ToString(),
                            Origen = reader[i]["origen"].ToString(),
                            Accion = reader[i]["accion"].ToString(),
                            Externo = reader[i]["externo"].ToString()
                        });
                       
                    }
                }
            }


            catch (Exception)
            {
                throw;
            }

            return consultas;
        }
        public ConsultaMedica GetConsultaByCodConsultaAndCi(string codConsulta, string ci)
        {

            string sqlsentence = "SELECT * FROM [Consulta] WHERE cod_consulta = @CodConsulta and ci_empleado = @CiEmpleado";

            dbManager.Command.CommandText = sqlsentence;
            dbManager.Command.Parameters.AddWithValue("@CodConsulta", codConsulta);
            dbManager.Command.Parameters.AddWithValue("@CiEmpleado", ci);

            ConsultaMedica resp = null;

            try
            {
                DataSet dataSet = dbManager.Query();

                if (dataSet.Tables[0].Rows.Count > 0)
                {
                    DataRow reader = dataSet.Tables[0].Rows[0];

                    resp = new ConsultaMedica
                    {
                        CodConsulta = Convert.ToInt32(reader["cod_consulta"], System.Globalization.CultureInfo.InvariantCulture),
                        CiEmpleado = Convert.ToInt32(reader["ci_empleado"], System.Globalization.CultureInfo.InvariantCulture),
                        Fecha = reader["fecha"].ToString(),
                        AreaTrabajo = reader["area_trabajo"].ToString(),
                        CodigoEmbol = Convert.ToInt32(reader["codigo_embol"], System.Globalization.CultureInfo.InvariantCulture),
                        Patologia = reader["patologia"].ToString(),
                        Gravedad = reader["gravedad"].ToString(),
                        Origen = reader["origen"].ToString(),
                        Accion = reader["accion"].ToString(),
                        Externo = reader["externo"].ToString()

                    };
                }
            }
            catch (Exception)
            {
                throw;
            }

            return resp;
        }

        public void EditarConsulta(ConsultaMedica consulta)
        {
            if (consulta == null)
            {
                throw new LoginException("The consulta can´t be null");
            }

            string sqlSentence =
                "UPDATE [Consulta]  SET [fecha] = @Fecha, [area_trabajo] = @AreaTrabajo, [codigo_embol] = @CodigoEmbol, ";
            sqlSentence = sqlSentence + "[patologia] = @Patologia, [gravedad] = @Gravedad, [origen] = @Origen, ";
            sqlSentence = sqlSentence + "[accion] = @Accion, [externo] = @Externo ";
            sqlSentence = sqlSentence + " WHERE cod_consulta = @CodConsulta and ci_empleado = @CiEmpleado";

            dbManager.Command.CommandText = sqlSentence;
            dbManager.Command.CommandType = System.Data.CommandType.Text;


            dbManager.Command.Parameters.AddWithValue("@Fecha", consulta.Fecha);
            dbManager.Command.Parameters.AddWithValue("@AreaTrabajo", consulta.AreaTrabajo);
            dbManager.Command.Parameters.AddWithValue("@CodigoEmbol", consulta.CodigoEmbol);
            dbManager.Command.Parameters.AddWithValue("@Patologia", consulta.Patologia);
            dbManager.Command.Parameters.AddWithValue("@Gravedad", consulta.Gravedad);
            dbManager.Command.Parameters.AddWithValue("@Origen", consulta.Origen);
            dbManager.Command.Parameters.AddWithValue("@Accion", consulta.Accion);
            dbManager.Command.Parameters.AddWithValue("@Externo", consulta.Externo);
            dbManager.Command.Parameters.AddWithValue("@CodConsulta", consulta.CodConsulta);
            dbManager.Command.Parameters.AddWithValue("@CiEmpleado", consulta.CiEmpleado);

            try
            {
                int value = dbManager.NonQuery();
                if (value < 1)
                {
                    throw new LoginException("The change wasn't registered");
                }

            }
            catch (Exception e)
            {
                throw new LoginException("Error edit profile", e);
            }
        }
    }
}
